﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using EHSubmit.Model;
using EHSubmit.Util;

namespace EHSubmit.Logic
{

    /// <summary>
    ///MessageManager 的摘要说明
    /// </summary>
    public class MessageManager      
    {
        private const string SQL_SELECT_Message = "SELECT Title,Content,SenderID,ReceiverID,SenderName,ReceiverName,SendTime,RecvTime,IsSViewed,IsRViewed,IsSDeleted,IsRDeleted FROM Message WHERE MID=@MID";
        private const string SQL_INSERT_Message = "INSERT INTO Message(Title,Content,SenderID,ReceiverID,SenderName,ReceiverName,SendTime,RecvTime,IsSViewed,IsRViewed,IsSDeleted,IsRDeleted) VALUES (@Title,@Content,@SenderID,@ReceiverID,@SenderName,@ReceiverName,@SendTime,@RecvTime,@IsSViewed,@IsRViewed,@IsSDeleted,@IsRDeleted)";
        private const string SQL_DELETE_BySender = "UPDATE Message SET IsSDeleted = 1 WHERE MID = @MID";       //删除到回收站
        private const string SQL_DELETE_ByReceiver = "UPDATE Message SET IsRDeleted = 1 WHERE MID = @MID";     //删除到回收站 
        private const string SQL_DELETE_BySender2 = "UPDATE Message SET IsSDeleted = 2 WHERE MID = @MID";       //从回收站删除
        private const string SQL_DELETE_ByReceiver2 = "UPDATE Message SET IsRDeleted = 2 WHERE MID = @MID";     //从回收站删除
        private const string SQL_DELETE = "DELETE FROM message WHERE MID=@MID";                                //信息实体的删除

        private const string SQL_SELECT_UserMessageByUser = "SELECT Count(*) as Num FROM Message,[User] WHERE ReceiverID=@UID AND IsRViewed = 0 AND [User].UID = ReceiverID AND [User].RoleId <> 0";
        private const string SQL_SELECT_AdminMessageByUser = "SELECT Count(*) as Num FROM Message,[User] WHERE ReceiverID=@UID AND IsRViewed = 0 AND [User].UID = ReceiverID AND [User].RoleId = 0";
        private const string PARM_UID = "@UID";
        private const string PARM_MID = "@MID";
        private const string PARM_Title = "@Title";
        private const string PARM_Content = "@Content";
        private const string PARM_SenderID = "@SenderID";
        private const string PARM_ReceiverID = "@ReceiverID";
        private const string PARM_SenderName = "@SenderName";
        private const string PARM_ReceiverName = "@ReceiverName";
        private const string PARM_SendTime = "@SendTime";
        private const string PARM_RecvTime = "@RecvTime";
        private const string PARM_IsSViewed = "@IsSViewed";
        private const string PARM_IsRViewed = "@IsRViewed";
        private const string PARM_IsSDeleted = "@IsSDeleted";
        private const string PARM_IsRDeleted = "@IsRDeleted";

        /// <summary>
        /// 构造Message的函数
        /// </summary>
        public MessageManager() { }


        /// <summary>
        /// 通过ID获取消息
        /// </summary>
        /// <param name="msgID">信息ID</param>
        /// <returns>返回单个消息</returns>

        public Message GetMessage(string msgID)
        {
            try
            {
                if ( Convert.ToInt32(msgID) <= 0)
                {
                    throw new Exception("消息ID不正确");
                }
                else
                {
                    Message message = new Message();
                    message.ID = Convert.ToInt32(msgID);
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    parm.Value = message.ID;
                    using (SqlDataReader rdrMessage = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_Message, parm))
                    {
                        if (rdrMessage.Read())
                        {
                            message.Title = rdrMessage["Title"] is DBNull ? string.Empty : (string)rdrMessage["Title"];                        //信息标题
                            message.Content = rdrMessage["Content"] is DBNull ? string.Empty : (string)rdrMessage["Content"];                  //信息内容
                            message.SenderID = rdrMessage["SenderID"] is DBNull ? string.Empty : (string)rdrMessage["SenderID"];               //发送者ID
                            message.ReceiverID = rdrMessage["ReceiverID"] is DBNull ? string.Empty : (string)rdrMessage["ReceiverID"];         //接收者ID
                            message.SenderName = rdrMessage["SenderName"] is DBNull ? string.Empty : (string)rdrMessage["SenderName"];         //发送者姓名
                            message.ReceiverName = rdrMessage["ReceiverName"] is DBNull ? string.Empty : (string)rdrMessage["ReceiverName"];   //接收者姓名
                            message.SendTime = rdrMessage["SendTime"] is DBNull ? DateTime.Now : (DateTime)rdrMessage["SendTime"];             //发送时间
                            message.RecvTime = rdrMessage["RecvTime"] is DBNull ? DateTime.Now : (DateTime)rdrMessage["RecvTime"];             //接收时间
                            message.IsSViewed = Convert.ToByte(rdrMessage["IsSViewed"]) == 0 ? false : true;                                   //发送者是否已读
                            message.IsRViewed = Convert.ToByte(rdrMessage["IsRViewed"]) == 0 ? false : true;                                   //接收者是否已读
                            message.IsSDeleted = rdrMessage["IsSDeleted"] is DBNull ? 0 : (int)rdrMessage["IsSDeleted"];                       //是否被发送者删除   0为未被删 1为删除到回收站 2为从回收站删除
                            message.IsRDeleted = rdrMessage["IsRDeleted"] is DBNull ? 0 : (int)rdrMessage["IsRDeleted"];                       //是否被接收者删除   0为未被删 1为删除到回收站 2为从回收站删除
                        }
                    }
                    return message;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
                return null;
            }
        }

        /// <summary>
        /// 获取消息集合
        /// </summary>
        /// <param name="msgIDList">要获取的消息的ID集合</param>
        /// <returns>返回消息集合</returns>
        public IList<Message> GetMessages(IList<string> msgIDList)
        {
            int i;
            IList<Message> msgList = new List<Message>();
            for (i = 0; i < msgIDList.Count; i++)
            {
                Message message = new Message();
                message = GetMessage(msgIDList[i]);
                msgList.Add(message);
            }
            return msgList;
        }

        /// <summary>
        /// Messege信息被添加
        /// </summary>
        /// <param name="Message">信息对象</param>
        public void AddMessage(EHSubmit.Model.Message message)
        {
            try
            {
                int i;
                SqlParameter[] parms = new SqlParameter[12];
                parms[0] = new SqlParameter(PARM_Title, SqlDbType.NChar, 100);
                parms[1] = new SqlParameter(PARM_Content, SqlDbType.NText);
                parms[2] = new SqlParameter(PARM_SenderID, SqlDbType.NChar, 13);
                parms[3] = new SqlParameter(PARM_ReceiverID, SqlDbType.NChar, 13);
                parms[4] = new SqlParameter(PARM_SenderName, SqlDbType.NChar, 13);
                parms[5] = new SqlParameter(PARM_ReceiverName, SqlDbType.NChar, 13);
                parms[6] = new SqlParameter(PARM_SendTime, SqlDbType.DateTime);
                parms[7] = new SqlParameter(PARM_RecvTime, SqlDbType.DateTime);
                parms[8] = new SqlParameter(PARM_IsSViewed, SqlDbType.Bit);
                parms[9] = new SqlParameter(PARM_IsRViewed, SqlDbType.Bit);
                parms[10] = new SqlParameter(PARM_IsSDeleted, SqlDbType.Int);
                parms[11] = new SqlParameter(PARM_IsRDeleted, SqlDbType.Int);
                parms[0].Value = message.Title;
                parms[1].Value = message.Content;
                parms[2].Value = message.SenderID;
                parms[3].Value = message.ReceiverID;
                parms[4].Value = message.SenderName;
                parms[5].Value = message.ReceiverName;
                parms[6].Value = Convert.ToDateTime(message.SendTime);
                parms[7].Value = Convert.ToDateTime(message.RecvTime);
                parms[8].Value = message.IsSViewed;
                parms[9].Value = message.IsRViewed;
                parms[10].Value = message.IsSDeleted;
                parms[11].Value = message.IsRDeleted;

                i = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_Message, parms);

            }
            catch
            {
                //不会啊~
            }
        }


        /// <summary>
        /// 接收者删除信息到回收站（单个）
        /// </summary>
        /// <param name="msgID">信息ID</param>
        public void RDeleteMessage(string msgID)                             //第一次删除
        {
            try
            {
                if (msgID == null)
                {
                    throw new Exception("作业信息ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(msgID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_ByReceiver, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 接收者删除信息到回收站（批量）
        /// </summary>
        /// <param name="msgIDList">信息ID集合</param>
        public void RDeleteMessages(IList<string> msgIDList)
        {
            for (int i = 0; i < msgIDList.Count; i++)
            {
                RDeleteMessage(msgIDList[i]);
            }
        }


        /// <summary>
        /// 接收者从回收站删除信息（单个）
        /// </summary>
        /// <param name="message">信息对象</param>
        public void RDeleteMessage_Completely(string msgID)                             //第一次删除
        {
            try
            {
                if (Convert.ToInt32(msgID) <= 0)
                {
                    throw new Exception("作业信息ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(msgID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_ByReceiver2, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 接收者从回收站删除信息（批量）
        /// </summary>
        /// <param name="messages">信息对象数组</param>
        //public void RDeleteMessage_Completely(Message[] messages)
        //{
        //    for (int i = 0; i < messages.Length; i++)
        //    {
        //        RDeleteMessage_Completely(messages[i]);
        //    }
        //}


        /// <summary>
        /// 接收者删除信息到回收站（单个）
        /// </summary>
        /// <param name="msgID">信息ID</param>
        public void SDeleteMessage(string msgID)                             //第一次删除
        {
            try
            {
                if (msgID == null)
                {
                    throw new Exception("作业信息ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(msgID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_BySender, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 接收者删除信息到回收站（批量）
        /// </summary>
        /// <param name="msgIDList">信息ID集合</param>
        public void SDeleteMessages(IList<string> msgIDList)
        {
            for (int i = 0; i < msgIDList.Count ; i++)
            {
                SDeleteMessage(msgIDList[i]);
            }
        }


        /// <summary>
        /// 发送者从回收站删除信息（单个）
        /// </summary>
        /// <param name="message">信息对象</param>
        public void SDeleteMessage_Completely(string msgID)                //第一次删除
        {
            try
            {
                if (Convert.ToInt32(msgID) <= 0)
                {
                    throw new Exception("作业信息ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(msgID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE_BySender2, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 发送者从回收站删除信息（批量）
        /// </summary>
        /// <param name="messages">作业信息对象数组</param>
        //public void SDeleteMessage_Completely(Message[] messages)
        //{
        //    for (int i = 0; i < messages.Length; i++)
        //    {
        //        SDeleteMessage_Completely(messages[i]);
        //    }
        //}

        /// <summary>
        /// 将消息从数据库中真正地删除
        /// </summary>
        /// <param name="msgID">要删除的消息ID</param>
        public void DeleleMessageReally(string msgID)
        {
            try
            {
                if (Convert.ToInt32(msgID) <= 0)
                {
                    throw new Exception("作业信息ID不能为空");
                }
                else
                {
                    SqlParameter parm = new SqlParameter(PARM_MID, SqlDbType.Int);
                    int nResult;
                    parm.Value = Convert.ToInt32(msgID);
                    nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_DELETE, parm);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log(ex.Message, ex);
            }
        }


        /// <summary>
        /// 信息标记已读
        /// </summary>
        /// <param name="msgIDList">要标记的已阅信息的ID</param>
        /// <param name="isReceiver">判断是否是接收者已阅</param>
        public void ViewMessage(IList<string> msgIDList,bool isReceiver)
        {
            try
            {
                if (msgIDList == null)
                {
                    throw new Exception("信息ID不能为空");
                }
                else
                {
                    
                    string SQL_VIEW_Message = null;
                    string sqlTemplate = "UPDATE Message SET {0} = 1 WHERE MID In ({1})";

                    StringBuilder conditMsgID = new StringBuilder();

                    //--要标记的已阅信息的ID
                    for (int i = 0; i < msgIDList.Count; i++)
                    {
                        conditMsgID.AppendFormat("{0},", msgIDList[i]);
                    }
                    conditMsgID.Remove(conditMsgID.Length - 1, 1);

                    if (isReceiver)
                    {
                        SQL_VIEW_Message = string.Format(sqlTemplate, "IsRViewed", conditMsgID);
                    }
                    else
                    {
                        SQL_VIEW_Message = string.Format(sqlTemplate, "IsSViewed", conditMsgID);
                    }
                    
                    int result;
                    result = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_VIEW_Message);
                }
            }
            catch
            { 
                //不会啊..
            }
        }

        /*--------------------------------------------------------------------------------------------
          string tbName ,                                             -- 表名
          string fieldsToReturn = '*',                                -- 需要返回的列    如列1，列2，...
          string fieldToOrder ='',                                    -- 排序的字段名    如表1 (不用加[])  
          int pageSize = 10,                                          -- 页尺寸
          int pageIndex = 1,                                          -- 页码
          bool doCount = false;                                       --返回记录总数, 非 0 值则返回
          bool IsDesc = true,                                          -- 设置排序类型, 非 0 值则降序
          string criteria = ''                                        -- 查询条件 (注意: 不要加 where ,如果是多条件，要将其作为一个整体传入，如（a=1 or b=2）外围的括号不可少)
         --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 获取单页的消息集合    默认记录数为10  
        /// </summary>
        /// <param name="UID">当前用户ID</param>
        /// <param name="pageIndex">当前页的页码</param>
        /// <param name="state">判断用户要获取的是收件箱或发件箱或垃圾箱的信息(0-收件箱、1-发件箱、2-垃圾箱)</param>
        /// <returns>消息集合</returns>
        public IList<Message> GetMessages(string UID, int pageIndex,int state)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "Message";
            pagedDataProvider.FieldsToReturn = "*";
            pagedDataProvider.PageSize = 15;                //默认记录数为10
            pagedDataProvider.PageIndex = pageIndex;
            pagedDataProvider.DoCount = false;
            pagedDataProvider.IsDesc = true;
            switch (state)
            {
                case 0:    //"inbox":
                    {
                        pagedDataProvider.Criteria = "ReceiverID = \'" + UID + "\' AND IsRDeleted = 0";
                        pagedDataProvider.FieldToOrder = "RecvTime";
                        break;
                    }
                case 1:    //"outbox":
                    {
                        pagedDataProvider.Criteria = "SenderID = \'" + UID + "\' AND IsSDeleted = 0";
                        pagedDataProvider.FieldToOrder = "SendTime";
                        break;
                    }
                case 2:   // "trash":
                    {
                        pagedDataProvider.Criteria = "((ReceiverID = \'" + UID + "\' AND IsRDeleted = 1) OR " + "(SenderID = \'" + UID + "\' AND IsSDeleted = 1))";
                        pagedDataProvider.FieldToOrder = "RecvTime";
                        break;
                    }
            }
            SqlDataReader rdrMessages = pagedDataProvider.GetResults();
            IList<Message> messageList = new List<Message>();
            try
            {
                while (rdrMessages.Read())
                {
                    Message message = new Message();

                    message.ID = (Int32)rdrMessages["MID"];                                                                              //信息ID
                    message.Title = rdrMessages["Title"] is DBNull ? string.Empty : (string)rdrMessages["Title"];                        //信息标题
                    message.Content = rdrMessages["Content"] is DBNull ? string.Empty : (string)rdrMessages["Content"];                  //信息内容
                    message.SenderID = rdrMessages["SenderID"] is DBNull ? string.Empty : (string)rdrMessages["SenderID"];               //发送者ID
                    message.ReceiverID = rdrMessages["ReceiverID"] is DBNull ? string.Empty : (string)rdrMessages["ReceiverID"];         //接收者ID
                    message.SenderName = rdrMessages["SenderName"] is DBNull ? string.Empty : (string)rdrMessages["SenderName"];         //发送者姓名
                    message.ReceiverName = rdrMessages["ReceiverName"] is DBNull ? string.Empty : (string)rdrMessages["ReceiverName"];   //接收者姓名
                    message.SendTime = rdrMessages["SendTime"] is DBNull ? DateTime.Now : (DateTime)rdrMessages["SendTime"];             //发送时间
                    message.RecvTime = rdrMessages["RecvTime"] is DBNull ? DateTime.Now : (DateTime)rdrMessages["RecvTime"];             //接收时间
                    message.IsSViewed = Convert.ToByte(rdrMessages["IsSViewed"]) == 0 ? false : true;                                    //发送者是否已读
                    message.IsRViewed = Convert.ToByte(rdrMessages["IsRViewed"]) == 0 ? false : true;                                    //接收者是否已读             
                    message.IsSDeleted = rdrMessages["IsSDeleted"] is DBNull ? 0 : (int)rdrMessages["IsSDeleted"];                       //是否被发送者删除   0为未被删 1为删除到回收站 2为从回收站删除
                    message.IsRDeleted = rdrMessages["IsRDeleted"] is DBNull ? 0 : (int)rdrMessages["IsRDeleted"];                       //是否被接收者删除   0为未被删 1为删除到回收站 2为从回收站删除

                    messageList.Add(message);
                }
            }
            finally
            {
                if (rdrMessages != null)   //关闭与数据库连接
                    rdrMessages.Close();
            }
            return messageList;
        }


        /*--------------------------------------------------------------------------------------------
            string tbName ,                                             -- 表名
            bool doCount = false;                                       --返回记录总数, 非 0 值则返回
            string criteria = ''                                        -- 查询条件 (注意: 不要加 where ,如果是多条件，要将其作为一个整体传入，如（a=1 or b=2）外围的括号不可少)
          --------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 获取信息总数量
        /// </summary>
        /// <param name="recvID">用户ID</param>
        /// <param name="state">判断用户要获取的是收件箱或发件箱或垃圾箱的信息(0-收件箱、1-发件箱、2-垃圾箱)</param>
        /// <returns>信息数量</returns>
        public int GetMessagePageCount(string UID,int state)
        {
            PagedDataProvider pagedDataProvider = new PagedDataProvider();
            pagedDataProvider.TbName = "Message";
            pagedDataProvider.DoCount = true;
            switch (state)
            {
                case 0:    //"inbox":
                    {
                        pagedDataProvider.Criteria = "ReceiverID = \'" + UID + "\' AND IsRDeleted = 0";
                        break;
                    }
                case 1:    //"outbox":
                    {
                        pagedDataProvider.Criteria = "SenderID = \'" + UID + "\' AND IsSDeleted = 0";
                        break;
                    }
                case 2:   // "trash":
                    {
                        pagedDataProvider.Criteria = "((ReceiverID = \'" + UID + "\' AND IsRDeleted = 1) OR " + "(SenderID = \'" + UID + "\' AND IsSDeleted = 1))";
                        break;
                    }
            }
            pagedDataProvider.TotalCount = pagedDataProvider.GetRecordCount();
            if (pagedDataProvider.TotalCount == -1)
            {
                //有待处理解决
                return -1;
            }
            else
            {
                return pagedDataProvider.TotalCount;
            }
        }


        /// <summary>
        /// 根据用户ID获取信息总数量与未读信息数量
        /// </summary>
        /// <param name="UID">用户ID</param>
        /// <returns>信息总数量与未读信息数量的数组</returns>
        public String[] MessageNumByUser(string UID)
        {
            try
            {
                String[] MessageNum = new string[2];
                SqlParameter parm = new SqlParameter(PARM_UID, SqlDbType.NVarChar);
                parm.Value = UID;                                                                       //应该写成一个，以后再改
                using (SqlDataReader rdrMessage = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_UserMessageByUser, parm))
                {
                    if (rdrMessage.Read())
                    {
                        MessageNum[0] = rdrMessage["Num"] is DBNull ? "0" : Convert.ToString(rdrMessage["Num"]);
                    }
                }
                
                using (SqlDataReader rdrMessage = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_AdminMessageByUser, parm))
                {
                    if (rdrMessage.Read())
                    {
                        MessageNum[1] = rdrMessage["Num"] is DBNull ? "0" : Convert.ToString(rdrMessage["Num"]);
                    }
                }
                return MessageNum;

            }
            catch (Exception e)
            {
                String[] MessageNum = new string[2];
                MessageNum[0] = "0";
                MessageNum[1] = "0";
                return MessageNum;
            }
        }


    }
}